package cn.tedu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.tedu.entity.Dept;
import cn.tedu.entity.Emp;
import cn.tedu.utils.DBUtils;

public class EmpDao {

	public List<Emp> findAll(){
		ArrayList<Emp> emps=new ArrayList<>();
		//获取连接
		try (Connection conn = DBUtils.getConn();) {
			String sql="select e.empno,e.ename,e.job,e.comm,d.deptno,d.dname,d.loc from emp e join dept d on e.deptno=d.deptno";
			Statement stat=conn.createStatement();
			ResultSet rs=stat.executeQuery(sql);
			while (rs.next()) {
				int empno=rs.getInt(1);
				String ename=rs.getString(2);
				String job=rs.getString(3);
				double comm=rs.getDouble(4);
				int deptno=rs.getInt(5);
				String dname=rs.getString(6);
				String loc=rs.getString(7);
				//封装到对象里
				Emp e=new Emp(empno, ename, comm, job, new Dept(deptno, dname, loc));
				//添加到集合
				emps.add(e);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return emps;
	}

	public void deleteByEmpno(String id) {
		//获取连接
		try (Connection conn = DBUtils.getConn();) {
			String sql="delete from emp where empno=?";
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, Integer.parseInt(id));
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void add(Emp emp) {
		//获取连接
		try (Connection conn = DBUtils.getConn();) {
			String sql="insert into emp(empno,ename,job,comm,deptno) values(?,?,?,?,?)";
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, emp.getEmpno());
			ps.setString(2, emp.getEname());
			ps.setString(3, emp.getJob());
			ps.setDouble(4, emp.getComm());
			ps.setInt(5, emp.getDept().getDeptno());
			ps.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Emp findEmpBympno(String empno) {
		//获取连接
		try (Connection conn = DBUtils.getConn();) {
			String sql="select e.empno,e.ename,e.job,e.comm,d.deptno,d.dname,d.loc from emp e join dept d on e.deptno=d.deptno where empno=?";
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, Integer.parseInt(empno));
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				String ename=rs.getString(2);
				String job=rs.getString(3);
				double comm=rs.getDouble(4);
				int deptno=rs.getInt(5);
				String dname=rs.getString(6);
				String loc=rs.getString(7);
				//封装到对象里
				Emp e=new Emp(Integer.parseInt(empno), ename, comm, job, new Dept(deptno, dname, loc));
				return e;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
		
		
	}

	public void undate(Emp emp) {
		//获取连接
		try (Connection conn = DBUtils.getConn();) {
			String sql="update emp set ename=?,job=?,comm=?,deptno=? where empno=?";
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1, emp.getEname());
			ps.setString(2, emp.getJob());
			ps.setDouble(3, emp.getComm());
			ps.setInt(4, emp.getDept().getDeptno());
			ps.setInt(5, emp.getEmpno());
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
